package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.CellFormat;
import jxl.Workbook;
import jxl.biff.FontRecord;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.Range; 
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.net.ntp.TimeStamp;
import org.apache.tools.ant.types.FileList.FileName;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.jboss.util.propertyeditor.BigDecimalEditor;
import org.joda.time.DateTime;

import com.lowagie.text.Font;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class FI_Form4 extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private String startDate = null;
	private String endDate = null;
	private String startDateMinusYear = null;
	private String endDateMinusYear = null;
	private String getSQlresult = null;
	private Timestamp tsEndDate = null;
	private Timestamp tsEndDate2 = null;
	private Timestamp tsStartDate = null;
	private Timestamp tsEndDateMinusYear = null;
	private Timestamp tsStartDateMinusYear = null;	
	private boolean Thousand = false;
	int year = 0;
	int year2 = 0;

	private static CLogger s_log = CLogger.getCLogger(BalanceSheets.class);
	/** */
	private String path = "";

	class line {
		BigDecimal v5000 = new BigDecimal(0);	BigDecimal v5000prev = new BigDecimal(0);
		BigDecimal v5100 = new BigDecimal(0);	BigDecimal v5100prev = new BigDecimal(0);
		BigDecimal v5200 = new BigDecimal(0);	BigDecimal v5200prev = new BigDecimal(0);
		BigDecimal v5300 = new BigDecimal(0);	BigDecimal v5300prev = new BigDecimal(0);
		BigDecimal v543000 = new BigDecimal(0);	BigDecimal v543000prev = new BigDecimal(0);
		BigDecimal v541000 = new BigDecimal(0);	BigDecimal v541000prev = new BigDecimal(0);
	}
	
	
	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			// else if (name.equals("startDate"))
			// tsStartDate = (Timestamp) para[i].getParameter();
			else if (name.equals("endDate"))
				tsEndDate = (Timestamp) para[i].getParameter();
			 else if (name.equals("Thousand"))
			 Thousand = ((String) para[i].getParameter()).equals("Y");
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		tsStartDate = new Timestamp(0);
		tsStartDateMinusYear = new Timestamp(0);
		tsEndDateMinusYear = new Timestamp(0);
		
		
		year = tsEndDate.getYear() + 1900;

		if(year == 2013){
			
			Timestamp ts = tsEndDate;
			
			Calendar c = Calendar.getInstance();
			c.setTimeInMillis(ts.getTime());
			c.add(Calendar.YEAR, -1);
			c.set(Calendar.DAY_OF_MONTH, 1);
			c.set(Calendar.MONTH, 0);
			tsStartDateMinusYear.setTime(c.getTimeInMillis());
			
			c = Calendar.getInstance();
			c.setTimeInMillis(ts.getTime());
			c.add(Calendar.YEAR, -1);
			tsEndDateMinusYear.setTime(c.getTimeInMillis());

			c = Calendar.getInstance();
			c.setTimeInMillis(ts.getTime());
//			c.add(Calendar.YEAR, 1);
			c.set(Calendar.DAY_OF_MONTH, 1);
			c.set(Calendar.MONTH, 0);
			tsStartDate.setTime(c.getTimeInMillis());
			
			c = Calendar.getInstance();
			c.setTimeInMillis(ts.getTime());
//			c.add(Calendar.YEAR, 1);
			tsEndDate.setTime(c.getTimeInMillis());
			int i = 1;
			
		}else{
			
			Calendar c = Calendar.getInstance();
			c.setTimeInMillis(tsEndDate.getTime());
			c.set(Calendar.DAY_OF_MONTH, 1);
			c.set(Calendar.MONTH, 0);
			
			tsStartDate.setTime(c.getTimeInMillis());
			
			c.add(Calendar.YEAR, -1);
			tsStartDateMinusYear.setTime(c.getTimeInMillis());
			
			c.setTimeInMillis(tsEndDate.getTime());
			c.add(Calendar.YEAR, -1);
			tsEndDateMinusYear.setTime(c.getTimeInMillis());			
		}
		
//		Date lEndDate = new Date(tsEndDate.getTime());
//		SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");
//		year = lEndDate.getYear() + 1900;
//		endDate = format.format(lEndDate);
// 
//		if(year == 2013)
//			endDateMinusYear = endDate;
//		else
//			endDateMinusYear = endDate.substring(0, 6) + String.valueOf(year - 1);
//
//		// lEndDate = new Date(tsStartDate.getTime());
//		// year = lEndDate.getYear() + 1900;
//		// startDate = format.format(lEndDate);
//		//
//		// startDateMinusYear = startDate.substring(0, 6) + String.valueOf(year
//		// - 1);
//		int tmpYear = 0;
//		year2 = year;
//		
//		if(year == 2013) 
//			year = 2012;
//		
//		String endDate2 = "01.01.2013";
//		startDate = "01.01." + String.valueOf(year);
//		if(year2 == 2013)
//			startDateMinusYear = "01.01." + String.valueOf(year);
//		else
//			startDateMinusYear = "01.01." + String.valueOf(year - 1);
//		// endDateMinusYear = endDate.substring(0, 6)
//		// + String.valueOf(year - 1);
//
//		// ---------- get Timestamp dates ---------------{
//		Date d = null;
//		Date d2 = null;
//		Date d3 = null;
//		Date d4 = null;
//		try {
//			d = format.parse(startDate);
//			d2 = format.parse(startDateMinusYear);
//			d3 = format.parse(endDateMinusYear);
//			d4 = format.parse(endDate2);
//		} catch (ParseException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
////		d.parse(endDate);
//		tsStartDate = new Timestamp(d.getTime());
//		tsStartDateMinusYear = new Timestamp(d2.getTime());
//		tsEndDateMinusYear = new Timestamp(d3.getTime());
//		tsEndDate2 = new Timestamp(d4.getTime());
//		//}
		
		
		path = Util.localFilePath;
		// / here another code)
	}

	@Override
	protected String doIt() throws Exception {
		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName;
		if(Thousand)
			templateName = "FI_Form4_tmpl_thousand";
		else	
			templateName = "FI_Form4_tmpl";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate") + " " + templateName);

		//if(System.get)
			
		
		File templateCopy = new File(Util.localFilePath + "/" + "FI_Form4.xls");
		File temporaryFile = entry.getFile(Util.localFilePath + "/" + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}

		
		String org_name;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sql = new StringBuilder(
				"select name from ad_org where ad_org_id = 1000000");
		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				org_name = rs.getString(1);
				WritableFont arial10ptBold = new WritableFont(
						WritableFont.TIMES, 9, WritableFont.NO_BOLD);
				WritableCellFormat captionStyle = new WritableCellFormat(
						arial10ptBold);
				label = new Label(0, 4,
						("Наименование организации: " + org_name), captionStyle);
				sheet.addCell(label);

			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_Form4", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		// ExcelCell cellStart = Utils.getCellStart(tableBook,">>");
		// Style for value column
		WritableFont arial10ptBold = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.BOLD);
		WritableCellFormat valuestyle = null;
		if(Thousand)
			valuestyle = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_INTEGER);
		else
			valuestyle = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_FLOAT); 		
		// WritableCellFormat valuestyle = new WritableCellFormat();
		valuestyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setAlignment(Alignment.RIGHT);
		valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		valuestyle.setBackground(Colour.getInternalColour(44));
		valuestyle.setWrap(true);

		WritableFont arial10ptNoBold = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.NO_BOLD);
		WritableCellFormat  valuestyleNoBold = null;
		if(Thousand)
			valuestyleNoBold = new WritableCellFormat(arial10ptNoBold,NumberFormats.THOUSANDS_INTEGER);
		else
			valuestyleNoBold = new WritableCellFormat(arial10ptNoBold,NumberFormats.THOUSANDS_FLOAT);		

		valuestyleNoBold.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setAlignment(Alignment.RIGHT);
		valuestyleNoBold.setVerticalAlignment(VerticalAlignment.CENTRE);
		valuestyleNoBold.setWrap(true);
//		
//		for(int i=1; i<100;i++){
//			WritableCellFormat valuestyle22 = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_INTEGER);;
//			valuestyle22.setBackground(Colour.getInternalColour(i));
//			label = new Label(10, i, Integer.valueOf(i).toString(), valuestyle22);
//			sheet.addCell(label);
//		}
		
		if(!Thousand){
			WritableCellFormat valuestyle3 = new WritableCellFormat();
			label = new Label(8, 8, "", valuestyle3);
			sheet.addCell(label);
		}
		
//		List<line> l = getDatas();
//		if (getSQlresult != null)
//			return Msg.translate(m_ctx, "Ошибка запроса.");

		Calendar c = Calendar.getInstance();
		c.setTimeInMillis(tsEndDate.getTime());
		
		String caption = " за период, заканчивающийся "
				+ c.get(Calendar.DAY_OF_MONTH)
				+ " "
				+ org.utils.DataUtils.getMonthNameRU(
						new Date(tsEndDate.getTime())).toLowerCase() + " "
				+ c.get(Calendar.YEAR) + " года.";

		WritableFont arial10ptBold2 = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.BOLD);
		WritableCellFormat captionStyle = new WritableCellFormat(arial10ptBold2);
		captionStyle.setAlignment(Alignment.CENTRE);
		captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);

		label = new Label(0, 7, caption, captionStyle);
		sheet.addCell(label);

		// output datas ----------------------------------{
//		prev year
		double v5000prev = .0;
		if(year != 2013)
			v5000prev = getValue(tsStartDateMinusYear, null, "50").doubleValue(); 		
		number = new Number(2, 11, v5000prev, valuestyle);
		sheet.addCell(number);

		double v5100prev = getValue(tsStartDateMinusYear, null, "5100").doubleValue(); 
		number = new Number(3, 11, v5100prev, valuestyle);
		sheet.addCell(number);
		
		double v5200prev = getValue(tsStartDateMinusYear, null, "5200").doubleValue(); 
		number = new Number(4, 11, v5200prev, valuestyle);
		sheet.addCell(number);
		
		double v5300prev = getValue(tsStartDateMinusYear, null, "5300").doubleValue(); 
		number = new Number(5, 11, v5300prev, valuestyle);
		sheet.addCell(number);
		
		double tmpprev = .0;
		double v541000prev = .0;
		if(year != 2013)
			v541000prev = getValue(tsStartDateMinusYear, null, "541,543").doubleValue(); 
//		tmpprev = v541000prev;
		number = new Number(6, 11, v541000prev, valuestyle);
		sheet.addCell(number);		

		double new541prev = .0;
		new541prev = getValue(tsStartDateMinusYear, tsEndDateMinusYear, "541,543").doubleValue(); 
//		new541prev = new541prev - tmpprev;
		number = new Number(6, 15, new541prev, valuestyleNoBold);
		sheet.addCell(number);	
		
		new541prev = getValue(tsStartDateMinusYear, tsEndDateMinusYear, "50").doubleValue();
		number = new Number(2, 15, new541prev, valuestyleNoBold);
		sheet.addCell(number);			
		
		
		
//		current year-------------------------------------------------------------------------------------
		double v5000 = getValue(tsStartDate, null, "50").doubleValue(); 
		number = new Number(2, 42, v5000, valuestyle);
		sheet.addCell(number);
		
		double v5100 = getValue(tsStartDate, null, "5100").doubleValue(); 
		number = new Number(3, 42, v5100, valuestyle);
		sheet.addCell(number);
		
		double v5200 = getValue(tsStartDate, null, "5200").doubleValue(); 
		number = new Number(4, 42, v5200, valuestyle);
		sheet.addCell(number);
		
		double v5300 = getValue(tsStartDate, null, "5300").doubleValue(); 
		number = new Number(5, 42, v5300, valuestyle);
		sheet.addCell(number);
		
		double tmp;
		double v541000 = getValue(tsStartDate, null, "541,543").doubleValue(); 
		tmp = v541000;
		number = new Number(6, 42, v541000, valuestyle);
		sheet.addCell(number);		

		double new541 = .0;
		new541 = getValue(tsStartDate, tsEndDate, "541,543").doubleValue(); 
		number = new Number(6, 46, new541, valuestyleNoBold);
		sheet.addCell(number);				
		
		new541 = .0;
		new541 = getValue(tsStartDate, tsEndDate, "50").doubleValue(); 
		number = new Number(2, 46, new541, valuestyleNoBold);
		sheet.addCell(number);		
		
		// output datas -----------------------------------------}
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();

		try {
			String fileName = Util.localFilePath + "/" + "FI_Form4.xls";
			Util.launchFile(new File(fileName));
			//String command = "excel \"" + fileName + "\"";
			//Runtime.getRuntime().exec("cmd /c start " + command +" /t");
		} catch (Exception e) {
			e.printStackTrace();
		}

		return Msg.translate(m_ctx, "Success");
	}

//	private List<line> getDatas() {
//		List<line> list = new ArrayList<line>();
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//
//		StringBuilder sql = getSQl(tsStartDate, tsEndDate, tsStartDateMinusYear,
//				tsEndDateMinusYear);
//
//		try {
//			pstmt = DB.prepareStatement(sql.toString(), null);
//
//			rs = pstmt.executeQuery();
//			while (rs.next()) {
//				line l = new line();
//				l.v5000 = (rs.getBigDecimal(1) == null ? new BigDecimal(0) : rs.getBigDecimal(1));
//				l.v5100 = (rs.getBigDecimal(2) == null ? new BigDecimal(0) : rs.getBigDecimal(2));
//				l.v5200 = (rs.getBigDecimal(3) == null ? new BigDecimal(0) : rs.getBigDecimal(3));
//				l.v5300 = (rs.getBigDecimal(4) == null ? new BigDecimal(0) : rs.getBigDecimal(4));
//				l.v543000 = (rs.getBigDecimal(5) == null ? new BigDecimal(0) : rs.getBigDecimal(5));
//				l.v541000 = (rs.getBigDecimal(6) == null ? new BigDecimal(0) : rs.getBigDecimal(6));
//				l.v5000prev = (rs.getBigDecimal(7) == null ? new BigDecimal(0) : rs.getBigDecimal(7));
//				l.v5100prev = (rs.getBigDecimal(8) == null ? new BigDecimal(0) : rs.getBigDecimal(8));
//				l.v5200prev = (rs.getBigDecimal(9) == null ? new BigDecimal(0) : rs.getBigDecimal(9));
//				l.v5300prev = (rs.getBigDecimal(10) == null ? new BigDecimal(0) : rs.getBigDecimal(10));
//				l.v543000prev = (rs.getBigDecimal(11) == null ? new BigDecimal(0) : rs.getBigDecimal(11));
//				l.v541000prev = (rs.getBigDecimal(12) == null ? new BigDecimal(0) : rs.getBigDecimal(12));
//				list.add(l);
//			}
//		} catch (SQLException e) {
//			s_log.log(Level.SEVERE, "Report: FI_Form2", e);
//			getSQlresult = e.getMessage();
//		} finally {
//			DB.close(rs, pstmt);
//			rs = null;
//			pstmt = null;
//		}
//		return list;
//	}

//	private BigDecimal getNew541(Timestamp startDate, Timestamp endDate) {
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		BigDecimal n541 = new BigDecimal(0);
//		String sql = 
//				  
//				  "			select case when (sum(fa.amtacctcr - fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr - fa.amtacctdr)) end	\n"
//				+ "			from fact_acct fa, c_elementvalue ce 	\n"
//				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
//				+ "						and fa.dateacct between '" + startDate + "' and '"+ endDate +"'	\n"
//				+ "						and ce.value like '541%'" 
//				;
//			
//
//		try {
//			pstmt = DB.prepareStatement(sql, null);
//
//			rs = pstmt.executeQuery();
//			while (rs.next()) {
//				n541 = rs.getBigDecimal(1);
//			}
//		} catch (SQLException e) {
//			s_log.log(Level.SEVERE, "Report: FI_Form4", e);
//			getSQlresult = e.getMessage();
//			return new BigDecimal(0);
//		} finally {
//			DB.close(rs, pstmt);
//			rs = null;
//			pstmt = null;
//		}
//		return n541;
//		
//	}

	private BigDecimal getValue(Timestamp startDate, Timestamp endDate, String codes) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BigDecimal result = new BigDecimal(0);
		
		String[] words = codes.split(",");  
		
		String whereCodes = "";
		if(words.length > 0){
			whereCodes = " and ( ";
			for(int i=0; i < words.length; i++){
				if(i == (words.length-1))
					whereCodes += "	ce.value like '"+words[i]+"%'	"; 
				else
					whereCodes += "	ce.value like '"+words[i]+"%' or ";
					
			}
			whereCodes += " ) ";
				
		}

		
		String sql = 
				  
				  "			select coalesce(sum(fa.amtacctcr - fa.amtacctdr),0)	\n"
				+ "			from fact_acct fa, c_elementvalue ce 	\n"
				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
				;

		if(startDate != null && endDate != null){	
			sql +=	"		and fa.dateacct between '"+startDate+"'::date and '"+endDate+"'::date	\n"	;
		}else if(endDate == null){
			sql +=		"		and fa.dateacct <= '"+startDate+"'::date 	\n"	;
		}if(startDate == null){
			sql +=		"		and fa.dateacct <= '"+endDate+"'::date	\n"	;
		}
		
			sql += whereCodes;				

		

		try {
			pstmt = DB.prepareStatement(sql, null);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getBigDecimal(1);
				if(Thousand)
					if(result.compareTo(Env.ZERO) != 0)
						result = result.divide(new BigDecimal(1000));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_Form4", e);
			getSQlresult = e.getMessage();
			return new BigDecimal(0);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return result;
		
	}
	
	
//	private StringBuilder getSQl(Timestamp startDate, Timestamp endDate,
//			Timestamp startDateMinusYear, Timestamp endDateMinusYear) {
//		StringBuilder sql = new StringBuilder();
//		sql.append("select \n"
//				+ "v5000	\n"
//				+ ",v5100 \n"
//				+ ",v5200 \n"
//				+ ",v5300 \n"
//				+ ",v543000 \n"
//				+ ",v541000	\n"
//
//				+ ",v5000prev	\n"
//				+ ",v5100prev	\n"
//				+ ",v5200prev	\n"
//				+ ",v5300prev	\n"
//				+ ",v543000prev	\n"
//				+ ",v541000prev	\n"
//				+ "\n"
//				+ "from(	\n"
//				+ "select 	\n"
//				+ "(select case when (sum(fa.amtacctcr - fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr - fa.amtacctdr)) end	\n"
//				+ "from fact_acct fa, c_elementvalue ce 	\n"
//				+ "where fa.account_id = ce.c_elementvalue_id 	\n"
//				+ "					and fa.dateacct between '" + startDate + "' and '"+ endDate +"'	\n"
//				+ "					and ce.value like '50%') v5000	\n"
//				+ "\n"
//				+ "		,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
//				+ "		from fact_acct fa, c_elementvalue ce 	\n"
//				+ "		where fa.account_id = ce.c_elementvalue_id \n"
//				+ "					and fa.dateacct between '" + startDate + "' and '"+ endDate +"'	\n"
//				+ "					and ce.value like '5100%') v5100	\n"
//				+ "\n"
//				+ "		,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
//				+ "		from fact_acct fa, c_elementvalue ce 	\n"
//				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
//				+ "						and fa.dateacct between '" + startDate + "' and '"+ endDate +"'	\n"
//				+ "						and ce.value like '5200%') v5200	\n"
//				+ "\n"
//				+ "			,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
//				+ "			from fact_acct fa, c_elementvalue ce 	\n"
//				+ "			where fa.account_id = ce.c_elementvalue_id \n"
//				+ "						and fa.dateacct between '" + startDate + "' and '"+ endDate +"'	\n"
//				+ "						and ce.value like '5300%') v5300	\n"
//				+ "						\n"
//				+ "			,(select case when (sum(fa.amtacctdr)) is null then 0 else (sum(fa.amtacctdr)) end \n"
//				+ "			from fact_acct fa, c_elementvalue ce 	\n"
//				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
//				+ "						and fa.dateacct between '" + startDate + "' and '"+ endDate +"'	\n"
//				+ "						and ce.value like '543%'" 
//				+ "				group by fa.dateacct " 			
//				+ "				order by fa.dateacct " 
//				+ " 			OFFSET 0 FETCH FIRST 1 ROWS ONLY		" 
//				+ ") v543000	\n"
//				+ "						\n"
//				
//				+ "			,(select case when (sum(fa.amtacctcr - fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr - fa.amtacctdr)) end	\n"
//				+ "			from fact_acct fa, c_elementvalue ce 	\n"
//				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
//				+ "						and fa.dateacct between '" + startDate + "' and '"+  endDate +"'	\n"
//				+ "						and (ce.value like '541%' or ce.value like '543%') " 
//				+ "			) v541000	\n"
//				
////				+ "			,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
////				+ "			from fact_acct fa, c_elementvalue ce 	\n"
////				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
////				+ "						and fa.dateacct between '" + startDate + "' and '"+ endDate +"'	\n"
////				+ "						and ce.value like '541%'" 
////				+ "				group by fa.dateacct " 			
////				+ "				order by fa.dateacct " 
////				+ " 			OFFSET 0 FETCH FIRST 1 ROWS ONLY		" 
////				+ "			) v541000	\n"
//				+ "						\n"
//				+ "						\n"
//				+ "						\n"
//				+ "			,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
//				+ "			from fact_acct fa, c_elementvalue ce \n"
//				+ "			where fa.account_id = ce.c_elementvalue_id	\n"
//				+ "						and fa.dateacct between '" + startDateMinusYear + "' and '"+ endDateMinusYear +"'	\n"
//				+ "						and ce.value like '5000%') v5000prev	\n"
//				+ "						\n"
//				+ "			,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
//				+ "			from fact_acct fa, c_elementvalue ce \n"
//				+ "			where fa.account_id = ce.c_elementvalue_id	\n"
//				+ "						and fa.dateacct between '" + startDateMinusYear + "' and '"+ endDateMinusYear +"'	\n"
//				+ "						and ce.value like '5100%') v5100prev	\n"
//				+ "						\n"
//				+ "			,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
//				+ "			from fact_acct fa, c_elementvalue ce 	\n"
//				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
//				+ "						and fa.dateacct between '" + startDateMinusYear + "' and '"+ endDateMinusYear +"'	\n"
//				+ "						and ce.value like '5200%') v5200prev	\n"
//				+ "						\n"
//				+ "			,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
//				+ "			from fact_acct fa, c_elementvalue ce 	\n"
//				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
//				+ "						and fa.dateacct between '" + startDateMinusYear + "' and '"+ endDateMinusYear +"'	\n"
//				+ "						and ce.value like '5300%') v5300prev	\n"
//				+ "						\n"
////				+ "			,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
////				+ "			from fact_acct fa, c_elementvalue ce 	\n"
////				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
////				+ "						and fa.dateacct between '" + startDateMinusYear + "' and '"+ endDateMinusYear +"'	\n"
////				+ "						and ce.value like '543000%') v543000prev	\n"
////				+ "						\n"
////				+ "			,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
////				+ "			from fact_acct fa, c_elementvalue ce 	\n"
////				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
////				+ "						and fa.dateacct between '" + startDateMinusYear + "' and '"+ endDateMinusYear +"'	\n"
////				+ "						and ce.value like '541000%') v541000prev	\n"
//				+ "			,(select case when (sum(fa.amtacctdr)) is null then 0 else (sum(fa.amtacctdr)) end \n"
//				+ "			from fact_acct fa, c_elementvalue ce 	\n"
//				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
//				+ "						and fa.dateacct between '" + startDateMinusYear + "' and '"+ endDateMinusYear +"'	\n"
//				+ "						and ce.value like '543%'" 
//				+ "				group by fa.dateacct " 			
//				+ "				order by fa.dateacct " 
//				+ " 			OFFSET 0 FETCH FIRST 1 ROWS ONLY		" 
//				+ ") v543000prev	\n"
//				+ "						\n"
//				+ "			,(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end	\n"
//				+ "			from fact_acct fa, c_elementvalue ce 	\n"
//				+ "			where fa.account_id = ce.c_elementvalue_id 	\n"
//				+ "						and fa.dateacct between '" + startDateMinusYear + "' and '"+ endDateMinusYear +"'	\n"
//				+ "						and ce.value like '541%'" 
//				+ "				group by fa.dateacct " 			
//				+ "				order by fa.dateacct " 
//				+ " 			OFFSET 0 FETCH FIRST 1 ROWS ONLY		" 
//				+ "			) v541000prev	\n"
//				+ "	) as main	\n");
//
//		return sql;
//	}
}